package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Order;
import com.leexuan.ordersystem.beans.OrderDetail;
import com.leexuan.ordersystem.beans.Product;

public class OrderDB extends DB {

	private String ADD_ORDER = "INSERT INTO [order] VALUES (?,?,?,?,?,?)";

	private String GET_ORDER_NEW = "SELECT TOP 1 * FROM [order] ORDER BY oid DESC";

	private String GET_ORDER = "SELECT o.oid,o.[sum],o.dtype,o.aid,o.cid,a.name AS aname,c.name as cname,d.name as dname, CONVERT(VARCHAR(30),date,111) as date,CONVERT(VARCHAR(30),ddate,111) as ddate FROM [order] AS o INNER JOIN admin AS a ON o.aid=a.aid INNER JOIN customer AS c ON c.cid=o.cid  INNER JOIN dsort AS d ON o.dtype=d.dsid WHERE o.oid=?";

	private String GET_ORDERS = "SELECT o.oid,o.[sum],o.dtype,o.aid,o.cid,a.name AS aname,c.name as cname,d.name as dname ,CONVERT(VARCHAR(30),date,111) as date,CONVERT(VARCHAR(30),ddate,111) as ddate FROM [order] AS o INNER JOIN admin AS a ON o.aid=a.aid INNER JOIN customer AS c ON c.cid=o.cid  INNER JOIN dsort AS d ON o.dtype=d.dsid ";

	private String ADD_ORDER_DETAIL = "INSERT INTO orderdetail VALUES (?,?,?,?,?)";

	private String DEL_ORDER = "DELETE FROM [order] WHERE oid=?";

	private String DEL_ORDER_DETAIL = "DELETE FROM orderdetail WHERE oid=?";

	private String GET_ORDER_DETAIL = "SELECT p.pid,p.number,p.model,p.name,od.odid,od.quantity,od.remain,od.price,s.name AS sname FROM orderdetail AS od INNER JOIN product AS p ON od.pid=p.pid INNER JOIN sort AS s ON s.sid=p.sid WHERE od.oid=?";

	private String SUB_ORDER_DETAIL_REMAIN = "UPDATE orderdetail SET remain=remain-? WHERE odid=?";

	private String GET_ORDERS_DELIVER = "SELECT  DISTINCT o.oid,CONVERT(VARCHAR(30),o.date,111) as date,  CONVERT(VARCHAR(30),o.ddate,111) as ddate,o.sum,c.name as cname,ds.name as dname FROM [order] AS o INNER JOIN  deliver AS d ON o.oid=d.oid INNER JOIN customer AS c ON o.cid=c.cid INNER JOIN dsort AS ds ON ds.dsid=o.dtype";

	private String GET_ORDERS_FULL = "SELECT o.oid,o.[sum],o.dtype,o.aid,o.cid,a.name AS aname,c.name as cname,d.name as dname ,CONVERT(VARCHAR(30),date,111) as date,CONVERT(VARCHAR(30),ddate,111) as ddate FROM [order] AS o INNER JOIN admin AS a ON o.aid=a.aid INNER JOIN customer AS c ON c.cid=o.cid  INNER JOIN dsort AS d ON o.dtype=d.dsid WHERE oid NOT IN (SELECT DISTINCT oid FROM orderdetail WHERE remain>0)";

	private String GET_ORDERS_NOTFULL = "SELECT o.oid,o.[sum],o.dtype,o.aid,o.cid,a.name AS aname,c.name as cname,d.name as dname ,CONVERT(VARCHAR(30),date,111) as date,CONVERT(VARCHAR(30),ddate,111) as ddate FROM [order] AS o INNER JOIN admin AS a ON o.aid=a.aid INNER JOIN customer AS c ON c.cid=o.cid  INNER JOIN dsort AS d ON o.dtype=d.dsid WHERE oid IN (SELECT DISTINCT oid FROM orderdetail WHERE remain>0)";

	public boolean addOrder(Order ad) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_ORDER);
			pstmt.setString(1, ad.getDate());
			pstmt.setFloat(2, ad.getSum());
			pstmt.setInt(3, ad.getDtype());
			pstmt.setString(4, ad.getDdate());
			pstmt.setInt(5, ad.getAid());
			pstmt.setInt(6, ad.getCid());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public Order getOrderNew() {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Order order = new Order();
		try {
			pstmt = conn.prepareStatement(GET_ORDER_NEW);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				order.setCid(rs.getInt("cid"));
				order.setAid(rs.getInt("aid"));
				order.setOid(rs.getInt("oid"));
				order.setDate(rs.getString("date"));
				order.setDdate(rs.getString("ddate"));
				order.setDtype(rs.getInt("dtype"));
				order.setSum(rs.getFloat("sum"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return order;
	}

	public Order getOrder(int oid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Order order = new Order();
		try {
			pstmt = conn.prepareStatement(GET_ORDER);
			pstmt.setInt(1, oid);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				order.setCid(rs.getInt("cid"));
				order.setAid(rs.getInt("aid"));
				order.setOid(rs.getInt("oid"));
				order.setDate(rs.getString("date"));
				order.setDdate(rs.getString("ddate"));
				order.setDtype(rs.getInt("dtype"));
				order.setSum(rs.getFloat("sum"));
				order.setAname(rs.getString("aname"));
				order.setDname(rs.getString("dname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return order;
	}

	public boolean addOrderDetail(OrderDetail ad) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_ORDER_DETAIL);
			pstmt.setInt(1, ad.getPid());
			pstmt.setInt(2, ad.getOid());
			pstmt.setInt(3, ad.getQuantity());
			pstmt.setFloat(4, ad.getPrice());
			pstmt.setInt(5, ad.getRemain());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean subOrderDetail(int sub, int odid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(SUB_ORDER_DETAIL_REMAIN);

			pstmt.setInt(1, sub);
			pstmt.setInt(2, odid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getOrders() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList orders = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_ORDERS, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Order order = new Order();

				order.setCid(rs.getInt("cid"));
				order.setAid(rs.getInt("aid"));
				order.setOid(rs.getInt("oid"));
				order.setDate(rs.getString("date"));
				order.setDdate(rs.getString("ddate"));
				order.setDtype(rs.getInt("dtype"));
				order.setSum(rs.getFloat("sum"));
				order.setAname(rs.getString("aname"));
				order.setCname(rs.getString("cname"));
				order.setDname(rs.getString("dname"));
				orders.add(order);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return orders;
	}

	public ArrayList getOrdersFull() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList orders = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_ORDERS_FULL, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Order order = new Order();

				order.setCid(rs.getInt("cid"));
				order.setAid(rs.getInt("aid"));
				order.setOid(rs.getInt("oid"));
				order.setDate(rs.getString("date"));
				order.setDdate(rs.getString("ddate"));
				order.setDtype(rs.getInt("dtype"));
				order.setSum(rs.getFloat("sum"));
				order.setAname(rs.getString("aname"));
				order.setCname(rs.getString("cname"));
				order.setDname(rs.getString("dname"));
				orders.add(order);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return orders;
	}

	public ArrayList getOrdersNotFull() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList orders = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_ORDERS_NOTFULL, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Order order = new Order();

				order.setCid(rs.getInt("cid"));
				order.setAid(rs.getInt("aid"));
				order.setOid(rs.getInt("oid"));
				order.setDate(rs.getString("date"));
				order.setDdate(rs.getString("ddate"));
				order.setDtype(rs.getInt("dtype"));
				order.setSum(rs.getFloat("sum"));
				order.setAname(rs.getString("aname"));
				order.setCname(rs.getString("cname"));
				order.setDname(rs.getString("dname"));
				orders.add(order);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return orders;
	}

	public boolean delOrder(int oid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_ORDER);

			pstmt.setInt(1, oid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean delOrderDetail(int oid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_ORDER_DETAIL);

			pstmt.setInt(1, oid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getOrderDetails(int oid) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList orderdetails = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_ORDER_DETAIL, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			pstmt.setInt(1, oid);

			rs = pstmt.executeQuery();

			while (rs.next()) {

				Product od = new Product();

				od.setModel(rs.getString("model"));
				od.setName(rs.getString("name"));
				od.setNum(rs.getInt("quantity"));
				od.setNumber(rs.getString("number"));
				od.setPrice(rs.getFloat("price"));
				od.setSname(rs.getString("sname"));
				od.setRemain(rs.getInt("remain"));
				od.setOdid(rs.getInt("odid"));
				od.setPid(rs.getInt("pid"));

				orderdetails.add(od);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return orderdetails;
	}

	public ArrayList getOrdersDeliver() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList orders = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_ORDERS_DELIVER, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Order order = new Order();

				order.setOid(rs.getInt("oid"));
				order.setDate(rs.getString("date"));
				order.setDdate(rs.getString("ddate"));
				order.setSum(rs.getFloat("sum"));
				order.setCname(rs.getString("cname"));
				order.setDname(rs.getString("dname"));
				orders.add(order);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return orders;
	}
}
